from flask import Flask,request

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
from flask_migrate import Migrate

# __name__: 当前 app.py这个模块
app = Flask(__name__)

HOSTNAME = "127.0.0.1"
PORT = 3306
USERNAME = "root"
PASSWORD = "root"
DATA_BASE = "database_learn"
app.config["SQLALCHEMY_DATABASE_URI"] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATA_BASE}?charset=utf8mb4"

# app.config中设置好连接数据库的信息
# 然后使用SQLAlchemy创建一个db对象
# SqlAlchemy 会自动读取 app.config 中的连接信息
db = SQLAlchemy(app)

migrate = Migrate(app, db)

# ORM Mapping 三部曲
# 1 flask db init 只需要执行一次
# 2 flask db migrate: 识别ORM模型的改变, 生成迁移脚本
# 3 flask db upgrade: 运行迁移脚本, 同步到数据库中

# ------------------------ 测试数据库连接是否正常 ------------------------
# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("SELECT 1"))
#         print(rs.fetchone())
# ------------------------ 测试数据库连接是否正常 ------------------------

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # varchar 100
    username = db.Column(db.String(100),  nullable=False)
    password = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100))
    signature = db.Column(db.String(100))

class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), )
    content = db.Column(db.Text,)
    # Author
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), )
    # 通过 backref 自动给User绑定一个 属性 叫做 "articles", 用来获取文章列表
    author = db.relationship('User',backref='articles')

# with app.app_context():
#     db.create_all()

@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


@app.route('/user/add',methods=['GET'])
def add_user():
    user1 = User(username="zhangsan", password="qert1234")
    user2 = User(username="lisi", password="qert1234")
    user3 = User(username="wangwu", password="qert1234")
    db.session.add(user1)
    db.session.add(user2)
    db.session.add(user3)
    db.session.commit()
    db.session.close()
    return "Created Successfully"

@app.route('/user/query',methods=['GET'])
def query_user():
    # 通过 id 查找
    # User 继承了 db.Model,所以有 query方法
    user = User.query.get(1)
    print(f"user: {user.username}, id: {user.id}, password: {user.password}  ")
    return {"username": user.username, "password": user.password, "id": user.id}

@app.route('/user/filter',methods=['GET'])
def filter_user():
    # User 继承了 db.Model,所以有 query方法
    # filter_by
    kw = request.args.get('kw')
    user_list = []
    # users = User.query.filter_by(username=kw)
    users = User.query.filter(User.username.like("%"+ kw+ "%"))
    user_list = [{"id": user.id, "username": user.username, "password": user.password} for user in users]

    return user_list

@app.route('/user/update',methods=['GET'])
def update_user():
    name = request.args.get('name')
    user = User.query.filter_by(username=name).first()
    user.username = "changed_" + user.username
    db.session.commit()
    return {"username": user.username, "id": user.id}

@app.route('/user/delete',methods=['DELETE'])
def delete_user():
    # find
    user_id = request.args.get('id')
    user = User.query.get(user_id)
    # delete from session
    db.session.delete(user)
    # delete from db
    db.session.commit()
    return "Deleted Successfully"

@app.route('/article/add',methods=['GET'])
def add_article():
    artile1 = Article(title="Flask Book", content="Flask Content")
    artile1.author = User.query.get(2)
    artile2 = Article(title="Django Book", content="Django Content")
    artile2.author = User.query.get(2)
    db.session.add_all([artile1, artile2])
    db.session.commit()
    return "Created Successfully"

@app.route('/article/query',methods=['GET'])
def query_article():
    author_id = request.args.get('author_id')
    if not author_id:
        return []
    user = User.query.get(int(author_id))
    res = {"author": user.username,"article":[]}
    for article in user.articles:
        res["article"].append({"title":article.title,"content":article.content})
    return res

if __name__ == '__main__':
    app.run(debug=True)
